package tt.dz.biz.dao.merch;

import java.util.Date;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;

@Repository("merchAccountDetailDAO")
public class MerchAccountDetailDAOImp
  extends BaseDAO
{
  private static final String INSERT_MERCH_ACCOUNT_DETAIL = "insert into ms_merch_account_detail(merchant_code,subject_code,sub_subject_code,loan_flag,amount,merchant_code1,accounting_date)  SELECT mad.merchant_code, mad.subject_code, mad.sub_subject_code, mad.loan_flag, sum(mad.amount) as amount, mad.merchant_code1,mad.accounting_date FROM  ms_merch_account_dividend AS mad  where mad. accounting_date=:accountingDate GROUP BY mad.merchant_code,mad.subject_code,mad.sub_subject_code,mad.loan_flag,mad.merchant_code1,mad.accounting_date ";
  private static final String QUERY_CHANGEAMOUNT_FOR_MERCH_ACCOUNT_DETAIL_BY_ACCOUNTINGDATE = "select  \tt.merchant_code,t.sub_subject_code,  abs(sum(t.borrow_amount)-sum(t.loan_amount)) change_amount from ( SELECT merchant_code, sub_subject_code, SUM(amount) as borrow_amount,  0 as loan_amount  FROM  ms_merch_account_detail WHERE loan_flag=0 \tand accounting_date = :accountingDate GROUP BY   \tmerchant_code, sub_subject_code  union all  SELECT \tmerchant_code, sub_subject_code,  0 as borrow_amount, SUM (amount) as loan_amount  FROM  ms_merch_account_detail WHERE loan_flag=1  and accounting_date =:accountingDate  GROUP BY merchant_code,sub_subject_code   ) as t GROUP BY t.merchant_code,t.sub_subject_code  ";
  private static final String QUERY_CHANGEAMOUNT_FOR_MERCHANT_SUBJECT_ACC = "SELECT merchant_code, sub_subject_code, abs(last_turnover) AS change_amount FROM ms_merchant_subject_acc ";
  private static final String QUERY_COUNT_ACCOUNT_DETAIL_FOR_DAY = "select count(1)  from  ms_merch_account_detail where accounting_date=:accountingDate";
  private static final String DELETE_ACCOUNT_DETAIL_FOR_DAY = "delete  from  ms_merch_account_detail where accounting_date=:accountingDate";
  
  public int accountDividendToDetail(Date accountingDate)
  {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("accountingDate", accountingDate);
    return save("insert into ms_merch_account_detail(merchant_code,subject_code,sub_subject_code,loan_flag,amount,merchant_code1,accounting_date)  SELECT mad.merchant_code, mad.subject_code, mad.sub_subject_code, mad.loan_flag, sum(mad.amount) as amount, mad.merchant_code1,mad.accounting_date FROM  ms_merch_account_dividend AS mad  where mad. accounting_date=:accountingDate GROUP BY mad.merchant_code,mad.subject_code,mad.sub_subject_code,mad.loan_flag,mad.merchant_code1,mad.accounting_date ", parameters);
  }
  
  public List<Map<String, Object>> queryChangeAmountForDetailByAccountingDate(Date accountingDate)
  {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("accountingDate", accountingDate);
    return queryForList("select  \tt.merchant_code,t.sub_subject_code,  abs(sum(t.borrow_amount)-sum(t.loan_amount)) change_amount from ( SELECT merchant_code, sub_subject_code, SUM(amount) as borrow_amount,  0 as loan_amount  FROM  ms_merch_account_detail WHERE loan_flag=0 \tand accounting_date = :accountingDate GROUP BY   \tmerchant_code, sub_subject_code  union all  SELECT \tmerchant_code, sub_subject_code,  0 as borrow_amount, SUM (amount) as loan_amount  FROM  ms_merch_account_detail WHERE loan_flag=1  and accounting_date =:accountingDate  GROUP BY merchant_code,sub_subject_code   ) as t GROUP BY t.merchant_code,t.sub_subject_code  ", parameters);
  }
  
  public List<Map<String, Object>> queryChangeAmountForSubjectAcc()
  {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    return queryForList("SELECT merchant_code, sub_subject_code, abs(last_turnover) AS change_amount FROM ms_merchant_subject_acc ", parameters);
  }
  
  public int queryCountDetailTotalForDay(Date accountingDate)
  {
    MapSqlParameterSource paramMap = new MapSqlParameterSource();
    paramMap.addValue("accountingDate", accountingDate);
    Number number = (Number)this.jdbcTemplate.queryForObject("select count(1)  from  ms_merch_account_detail where accounting_date=:accountingDate", paramMap, Integer.class);
    return number != null ? number.intValue() : 0;
  }
  
  public int deleteAccountDetailForDay(Date accountingDate)
  {
    MapSqlParameterSource paramMap = new MapSqlParameterSource();
    paramMap.addValue("accountingDate", accountingDate);
    return this.jdbcTemplate.update("delete  from  ms_merch_account_detail where accounting_date=:accountingDate", paramMap);
  }
}
